package com.github.wxiaoqi.security.common.biz;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Objects;

import javax.annotation.PostConstruct;

import org.beetl.core.GroupTemplate;
import org.beetl.core.Template;
import org.beetl.sql.core.BeetlSQLException;
import org.beetl.sql.core.ConnectionSource;
import org.beetl.sql.core.Interceptor;
import org.beetl.sql.core.InterceptorContext;
import org.beetl.sql.core.SQLManager;
import org.beetl.sql.core.SQLReady;
import org.beetl.sql.core.SQLResult;
import org.beetl.sql.core.SQLResultListener;
import org.beetl.sql.core.SQLScript;
import org.beetl.sql.core.SQLTableSource;
import org.beetl.sql.core.annotatoin.DateTemplate;
import org.beetl.sql.core.db.ClassDesc;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.core.db.TableDesc;
import org.beetl.sql.core.engine.PageQuery;
import org.beetl.sql.core.engine.SQLParameter;
import org.beetl.sql.core.kit.BeanKit;
import org.beetl.sql.core.kit.StringKit;
import org.beetl.sql.core.mapper.BaseMapper;
import org.beetl.sql.core.mapping.BeanProcessor;
import org.beetl.sql.core.query.LambdaQuery;
import org.springframework.beans.factory.annotation.Autowired;

import com.github.wxiaoqi.security.common.msg.TableResultResponse;
import com.github.wxiaoqi.security.common.util.EntityUtils;
import com.github.wxiaoqi.security.common.util.Query;

/**
 * Created by Mr.AG
 * Date: 17/1/13
 * Time: 15:13
 * Version 1.0.0
 */
public abstract class BaseBiz<T> {

	private  String lineSeparator = System.getProperty("line.separator", "\n");
	
	@Autowired
	private BaseMapper<T> mapper;
	
	private SQLManager sqlManager;
	
	private Template deleteTemplate;
	
	private String deleteTemplateId;
	
	@PostConstruct
	private void init() {
		sqlManager = mapper.getSQLManager();
    	Class<T> cls = mapper.getTargetEntity();
    	String tableName = sqlManager.getNc().getTableName(cls);
    	TableDesc table = sqlManager.getMetaDataManager().getTable(tableName);
    	DBStyle dbStyle = sqlManager.getDbStyle();
    	int db = dbStyle.getDBType();
    	//目前仅先实现mysql版本的
    	if(DBStyle.DB_MYSQL==db) {
    		MySqlStyle mySqlStyle = (MySqlStyle)dbStyle;
    		StringBuilder condition = new StringBuilder("delete from ");
            if(table.getSchema() != null) {
            	condition.append(table.getSchema()).append(".");
            }
            condition.append(tableName);
            condition.append(" where 1=1 " + lineSeparator);
    		ClassDesc classDesc = table.getClassDesc(cls, sqlManager.getNc());
            Iterator<String> cols = classDesc.getInCols().iterator();
            Iterator<String> attrs = classDesc.getAttrs().iterator();
            String prefix = "";
            String connector = " and ";
            while (cols.hasNext() && attrs.hasNext()) {
                String col = cols.next();
                String attr = attrs.next();
                String sql;
                if (classDesc.isDateType(attr)) {
                        DateTemplate dateTemplate = BeanKit.getAnnoation(classDesc.getTargetClass(), attr, DateTemplate.class);
                        if (dateTemplate == null){
                            sql = mySqlStyle.STATEMENT_START + "if(!isEmpty(" + prefix + attr + ")){"
                                    + mySqlStyle.STATEMENT_END + connector + mySqlStyle.getKeyWordHandler().getCol(col) + "=" + mySqlStyle.HOLDER_START + prefix + attr
                                    + mySqlStyle.HOLDER_END + lineSeparator + mySqlStyle.STATEMENT_START + "}" + mySqlStyle.STATEMENT_END;
                        } else {
                            String accept = dateTemplate.accept();
                            String[] vars = null;
                            if (accept == null || accept.length() == 0) {
                                String col1 = col.substring(0, 1).toUpperCase() + col.substring(1);
                                vars = new String[]{DateTemplate.MIN_PREFIX + col1, DateTemplate.MAX_PREFIX + col1};
                            } else {
                                vars = dateTemplate.accept().split(",");
                            }

                            String[] comp = null;
                            String compare = dateTemplate.compare();
                            if (compare == null || compare.length() == 0) {
                                comp = new String[]{DateTemplate.LARGE_OPT, DateTemplate.LESS_OPT};

                            } else {
                                comp =  dateTemplate.compare().split(",");
                            }

                            sql = mySqlStyle.STATEMENT_START + "if(!isEmpty(" + prefix + vars[0] + ")){"
                                    + mySqlStyle.STATEMENT_END + connector + col + comp[0] + mySqlStyle.HOLDER_START + vars[0] + mySqlStyle.HOLDER_END + lineSeparator + mySqlStyle.STATEMENT_START + "}" + mySqlStyle.STATEMENT_END;

                            sql = sql + mySqlStyle.STATEMENT_START + "if(!isEmpty(" + prefix + vars[1] + ")){"
                                    + mySqlStyle.STATEMENT_END + connector + col + comp[1] + mySqlStyle.HOLDER_START + vars[1] + mySqlStyle.HOLDER_END + lineSeparator + mySqlStyle.STATEMENT_START + "}" + mySqlStyle.STATEMENT_END;
                        }
                        condition.append(sql);
                } else {
                    sql = mySqlStyle.STATEMENT_START + "if(!isEmpty(" + prefix + attr + ")){"
                            + mySqlStyle.STATEMENT_END + connector + mySqlStyle.getKeyWordHandler().getCol(col) + "=" + mySqlStyle.HOLDER_START + prefix + attr
                            + mySqlStyle.HOLDER_END + lineSeparator + mySqlStyle.STATEMENT_START + "}" + mySqlStyle.STATEMENT_END;
                    condition.append(sql);
                }
            }
            
            SQLTableSource deleteSQLTableSource = new SQLTableSource(condition.toString());
          //slqId 保持与DefaultSQLIdNameConversion同样命名风格
            String className = StringKit.toLowerCaseFirstOne(cls.getSimpleName());
            deleteTemplateId = className + "." + "_gen_deleteObjectByTemplate";
            deleteSQLTableSource.setId(deleteTemplateId);
            sqlManager.getSqlLoader().addSQL(deleteTemplateId, deleteSQLTableSource);
            
            GroupTemplate gt = sqlManager.getBeetl().getGroupTemplate();
            deleteTemplate = gt.getTemplate(deleteTemplateId);
            
            deleteTemplate.binding("_manager", sqlManager);
            deleteTemplate.binding("_id", deleteTemplateId);
    	}
	}
	
    public LambdaQuery<T> createLambdaQuery(){
    	return mapper.createLambdaQuery();
    }
    
    public org.beetl.sql.core.query.Query<T> createQuery(){
    	return mapper.createQuery();
    }
    
    public T selectOne(T entity) {
        return mapper.templateOne(entity);
    }


    public T selectById(Object id) {
        return mapper.single(id);
    }


    public List<T> selectList(T entity) {
        return mapper.template(entity);
    }


    public List<T> selectListAll() {
        return mapper.all();
    }


    public Long selectCount(T entity) {
    	return mapper.templateCount(entity);
    }


    public void insert(T entity) {
        EntityUtils.setCreatAndUpdatInfo(entity);
        mapper.insert(entity);
    }


    public void insertSelective(T entity) {
        EntityUtils.setCreatAndUpdatInfo(entity);
        mapper.insertTemplate(entity);
    }

    /**
     * 不建议使用，请使用createLambdaQuery()或createQuery()
     * @param entity
     */
    @Deprecated
    public void delete(T entity) {
    	if(entity == null) {
    		return;
    	}
    	List<SQLParameter> jdbcPara = new LinkedList<SQLParameter>();
        
        deleteTemplate.binding("_root", entity);
        deleteTemplate.binding("_paras", jdbcPara);
        String jdbcSql = deleteTemplate.render();
        if(jdbcPara.size() < 1) {
        	System.err.println("不能所有的字段都为空！要清空数据么？请绕道三思！");
        	return;
        }
        System.out.println("执行delete sql:");
        System.out.println(jdbcSql);
        System.out.println("传入参数:");
        System.out.println(jdbcPara);
        int rs = 0;
        PreparedStatement ps = null;
        Connection conn = null;
        boolean isTransaction = false;
        try {
        	ConnectionSource connectionSource = sqlManager.getDs();
        	isTransaction = connectionSource.isTransaction();
            conn = connectionSource.getConn(deleteTemplateId, true, jdbcSql, jdbcPara);
            ps = conn.prepareStatement(jdbcSql);
            BeanProcessor bp = sqlManager.getDefaultBeanProcessors();
            bp.setPreparedStatementPara(deleteTemplateId, ps, jdbcPara);
            rs = ps.executeUpdate();
            System.out.println("返回delete结果:"+rs);
        } catch (SQLException e) {
            throw new BeetlSQLException(BeetlSQLException.SQL_EXCEPTION, e);
        } finally {
            clean(true,isTransaction, conn, ps, null);
        }
    }
    
    private void clean(boolean isUpdate,boolean isTransaction, Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            if (rs != null)
                rs.close();
            if (ps != null)
                ps.close();
            if (!isTransaction) {
                try {

                    if (conn != null) {
                        // colse 不一定能保证能自动commit
                        if (isUpdate && !conn.getAutoCommit()) {

                            conn.commit();
                        }

                        conn.close();
                    }
                } catch (SQLException e) {
                    throw new BeetlSQLException(BeetlSQLException.SQL_EXCEPTION, e);
                }

            }
        } catch (SQLException e) {
            // ignore
        }
    }


    public void deleteById(Object id) {
        mapper.deleteById(id);
    }


    public void updateById(T entity) {
        try {
			EntityUtils.setUpdatedInfo(entity);
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
        mapper.updateById(entity);
    }


    public void updateSelectiveById(T entity) {
        try {
			EntityUtils.setUpdatedInfo(entity);
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
        mapper.updateTemplateById(entity);

    }

//    @Deprecated
//    public List<T> selectByExample(Object example) {
//    	LambdaQuery<T> lq = mapper.createLambdaQuery();
//    	lq.addParam(example);
//    	return lq.select();
//    }

//    @Deprecated
//    public long selectCountByExample(Object example) {
//    	LambdaQuery<T> lq = mapper.createLambdaQuery();
//    	lq.addParam(example);
//        return lq.count();
//    }

    public TableResultResponse<T> selectByQuery(Query query) {
        LambdaQuery<T> lq = mapper.createLambdaQuery();
        if(query.entrySet().size()>0) {
            for (Map.Entry<String, Object> entry : query.entrySet()) {
            	lq.andLike(entry.getKey(), entry.getValue().toString());
            }
        }
        PageQuery<T> p = lq.page(query.getLimit(), query.getPage());
        return new TableResultResponse<T>(p.getTotalRow(), p.getList());
    }

}
